﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LoadNorthwindDB;

namespace Ex1.PlayWithNorthwind
{
    //Ex2 - DAO class with static methods 
    public class DAO
    {
        public static Customer CreateCustomer(string customerID, string companyName, string contactName = null,
            string contactTitle = null, string address = null, string city = null, string region = null, string postalCode = null,
            string country = null, string phone = null, string fax = null)
        {
            int customerIDLength = customerID.Length;
            if (customerIDLength != 5)
            {
                throw new ArgumentException("CustomerID must be 5 chars!");
            }

            Customer newCustomer = new Customer
                    {
                        CustomerID = customerID,
                        CompanyName = companyName,
                        ContactName = contactName,
                        ContactTitle = contactTitle,
                        Address = address,
                        City = city,
                        Region = region,
                        PostalCode = postalCode,
                        Country = country,
                        Phone = phone,
                        Fax = fax
                    };
            return newCustomer;
        }

        public static void InsertCustomer(Customer customerToInsert)
        {
            NorthwindEntities northwindEntities = new NorthwindEntities();
            try
            {
                using (northwindEntities)
                {
                    northwindEntities.AddToCustomers(customerToInsert);
                    northwindEntities.SaveChanges();
                }
                Console.WriteLine("Customer with ID = {0} was successfully inserted!", customerToInsert.CustomerID);
            }
            catch (System.Data.UpdateException)
            {
                Console.WriteLine("Cannot insert customer! Customer with ID = {0} is already in base!",
                    customerToInsert.CustomerID);
            }
        }

        public static Customer GetCustomerByID(string custID)
        {
            NorthwindEntities ent = new NorthwindEntities();
            using (ent)
            {
                Customer customer = ent.Customers.FirstOrDefault(c => c.CustomerID == custID);
                return customer;
            }
        }

        public static void UpdateCustomer(Customer cust)
        {
            NorthwindEntities northwindEntities = new NorthwindEntities();
            using (northwindEntities)
            {
                northwindEntities.Customers.Attach(cust);

                northwindEntities.ObjectStateManager.GetObjectStateEntry(cust).SetModified();
                northwindEntities.Refresh(System.Data.Objects.RefreshMode.ClientWins, cust);

                northwindEntities.SaveChanges();
            }
        }

        public static void DeleteCustomer(string customerID)
        {
            int customerIDLength = customerID.Length;
            if (customerIDLength != 5)
            {
                throw new ArgumentException("CustomerID must be 5 chars!");
            }
            NorthwindEntities northwindEntities = new NorthwindEntities();
            using (northwindEntities)
            {
                Customer customerToDelete = GetCustomerByID(northwindEntities, customerID);
                if (customerToDelete == null)
                {
                    throw new ArgumentNullException("Customer with ID = {0} does not exist!", customerID);
                }
                northwindEntities.Customers.DeleteObject(customerToDelete);

                northwindEntities.SaveChanges();
            }
            Console.WriteLine("Customer with ID = {0} was successfully deleted!", customerID);
        }

        private static Customer GetCustomerByID(NorthwindEntities ent, string customerID)
        {
            Customer customer = ent.Customers.FirstOrDefault(c => c.CustomerID == customerID);
            return customer;
        }
    }

    public class NorthwindTest
    {
        static void Main(string[] args)
        {
            //Customer cust = DAO.CreateCustomer("99999", "Telerik", "Pesho");
            //DAO.InsertCustomer(cust);
            //DAO.UpdateCustomer("99999", "Telerik", "Pesho Updated");
            //DAO.DeleteCustomer("99999");
            //FindCustomers();
            //FindCustomersNativeSQL();

            //DateTime startDate = new DateTime(1997,1,1);
            //DateTime endDate = new DateTime(1998, 12, 23);
            //FindSales("BC", startDate, endDate);

            //Customer cust = DAO.GetCustomerByID("99999");
            //cust.ContactName = "HUI";
            //DAO.UpdateCustomer(cust);

            //DAO.DeleteCustomer("99999");
        }

        //Ex3 - finds all customers who have orders made in 1997 and shipped to Canada
        static void FindCustomers()
        {
            NorthwindEntities ent = new NorthwindEntities();
            using (ent)
            {
                var filteredCustomers =
                    from cust in ent.Customers
                    join ord in ent.Orders on cust.CustomerID equals ord.CustomerID
                    where (ord.OrderDate.Value.Year == 1997 && ord.ShipCountry == "Canada")
                    select cust;
                foreach (var item in filteredCustomers)
                {
                    Console.WriteLine("ID: {0}, Customer Name: {1}", item.CustomerID, item.ContactName);
                }
            }
        }

        //Ex4 - Implement previous by using native SQL query and executing it through the ObjectContext
        static void FindCustomersNativeSQL()
        {
            NorthwindEntities northwindEntities = new NorthwindEntities();
            using (northwindEntities)
            {
                string nativeSQLQuery =
                    "SELECT c.CustomerID, c.CompanyName, o.OrderDate " +
                    "FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID " +
                    "WHERE YEAR (o.OrderDate) = 1997 AND o.ShipCountry = 'Canada'";
                IEnumerable<CustomerIDAndCompanyName> customers =
                    northwindEntities.ExecuteStoreQuery<CustomerIDAndCompanyName>(nativeSQLQuery);
                foreach (CustomerIDAndCompanyName cust in customers)
                {
                    Console.WriteLine("ID: {0,10}, Company: {1,-24}, Date: {2}",
                        cust.CustomerID, cust.CompanyName, cust.OrderDate);
                }
            }
        }

        private class CustomerIDAndCompanyName
        {
            public string CustomerID { get; set; }
            public string CompanyName { get; set; }
            public DateTime OrderDate { get; set; }
        }

        //Ex5 - Find all the sales by specified region and period (start / end dates)
        static void FindSales(string region, DateTime startDate, DateTime endDate)
        {
            NorthwindEntities ent = new NorthwindEntities();
            using (ent)
            {
                var filteredOrders =
                    from o in ent.Orders
                    where o.ShipRegion == region && 
                    o.ShippedDate.Value.Year >= startDate.Year &&
                    o.ShippedDate.Value.Month >= startDate.Month && 
                    o.ShippedDate.Value.Day >= startDate.Day &&

                    o.ShippedDate.Value.Year <= endDate.Year &&
                    o.ShippedDate.Value.Month <= endDate.Month &&
                    o.ShippedDate.Value.Day <= endDate.Day
                    select o;
                foreach (var order in filteredOrders)
                {
                    Console.WriteLine("{0} {1,5} {2,30}",order.OrderID,order.ShipRegion,order.ShippedDate);
                }
            }
        }
    }
}
